﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class RetirementDAL : IRetirementDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public RetirementDAL()
        {
        }
        public DataSet LoadRetirements(RetirementInfo reInfo)
        {
            string strSQL;
            int EmpTypeSeq = 0;

            if (reInfo.EmployeeType != null)
                EmpTypeSeq = reInfo.EmployeeType.Sequence;

            DataSet ds = null;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", reInfo.Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@EmpTypeSeq", EmpTypeSeq);

                strSQL = "SELECT r.*"
                        + ", et.EMP_TYPE_NAME, j.JOB_SITE_NAME, r.APPROVED_POSITION AS APPROVED_POSITIONS "
                        + ", CASE r.RETIRE_STATUS"
                            + " WHEN 'A' THEN 'อนุมัติแล้ว'"
                            + " WHEN 'W' THEN 'ยังไม่อนุมัติ'"
                            + " WHEN 'C' THEN 'ยกเลิก'"
                            + " ELSE ''"
                            + " END AS RETIRE_STATUS_NAME"
                    + " FROM HRM_PS_RETIREMENTS r"
                    + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON r.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON r.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN MAS_RECORD_STATUSES rs ON r.RETIRE_STATUS = rs.RECORD_STATUS"
                    + " WHERE r.RECORD_STATUS not like @DeleteRecord"
                        + (EmpTypeSeq > 0 ? " AND r.EMP_TYPE_SEQ = @EmpTypeSeq" : "")
                        + (reInfo.Sequence > 0 ? " AND r.RETIRE_SEQ = @Sequence" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
                return ds;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        //public IList<RetirementInfo> ListRetirements(RetirementInfo reInfo)
        //{
        //    string strSQL;
        //    int EmpTypeSeq = 0;

        //    if (reInfo.EmployeeType != null)
        //        EmpTypeSeq = reInfo.EmployeeType.Sequence;

        //    List<RetirementInfo> infoList = new List<RetirementInfo>();
        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(3);

        //        dbManager.AddParameters(0, "@Sequence", reInfo.Sequence);
        //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
        //        dbManager.AddParameters(2, "@EmpTypeSeq", EmpTypeSeq);

        //        strSQL = "SELECT r.RETIRE_SEQ, r.EMP_TYPE_SEQ, r.RETIRE_YEAR, r.START_DATE"
        //                + ", r.JOB_SITE_SEQ, r.REF_NO, r.RETIRE_TITLE, r.RETIRE_DESCR, r.APPROVED_DATE"
        //                + ", r.APPROVED_NAME, r.APPROVED_POSITION, r.RETIRE_STATUS, r.REMARKS"
        //                + ", r.RECORD_STATUS, r.CREATED_BY, r.UPDATED_BY"
        //                + ", CASE r.RETIRE_STATUS"
        //                    + " WHEN 'A' THEN 'อนุมัติแล้ว'"
        //                    + " WHEN 'W' THEN 'ยังไม่อนุมัติ'"
        //                    + " WHEN 'C' THEN 'ยกเลิก'"
        //                    + " ELSE ''"
        //                    + " END AS RETIRE_STATUS_NAME"
        //            + " FROM HRM_PS_RETIREMENTS r"
        //                //+ " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON r.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
        //                //+ " LEFT JOIN HRM_MP_JOB_SITES j ON r.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
        //                //+ " LEFT JOIN MAS_RECORD_STATUSES rs ON r.RETIRE_STATUS = rs.RECORD_STATUS"
        //            + " WHERE r.RECORD_STATUS not like @DeleteRecord"
        //                + (EmpTypeSeq > 0 ? " AND r.EMP_TYPE_SEQ = @EmpTypeSeq" : "")
        //                + (reInfo.Sequence > 0 ? " AND r.RETIRE_SEQ = @Sequence" : "");

        //        IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

        //        while (rdr.Read())
        //        {
        //            EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
        //            EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
        //            if (!rdr.IsDBNull(1))
        //            {
        //                IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(1)));
        //                etInfo = etList[0];
        //            }
                    
        //            JobSiteDAL jsDAL = new JobSiteDAL();
        //            JobSiteInfo jsInfo = new JobSiteInfo();
        //            if (!rdr.IsDBNull(4))
        //            {
        //                IList<JobSiteInfo> jsList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(4)));
        //                jsInfo = jsList[0];
        //            }
        //            MasRecordStatusDAL rsDAL = new MasRecordStatusDAL();
        //            MasRecordStatusInfo rsInfo = new MasRecordStatusInfo();
        //            if (!rdr.IsDBNull(11))
        //            {
        //                IList<MasRecordStatusInfo> rsList = rsDAL.ListRecordStatus(
        //                    new MasRecordStatusInfo(rdr.GetString(11)
        //                        , SystemInfo._StatusCommand));
        //                rsInfo = rsList[0];
        //            }

        //            RetirementInfo Info = new RetirementInfo(rdr.GetInt32(0)
        //                , etInfo
        //                , rdr.GetInt32(2)
        //                , rdr.IsDBNull(3) ? (DateTime?)null : rdr.GetDateTime(3)
        //                , jsInfo
        //                , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
        //                , rdr.IsDBNull(6) ? "" : rdr.GetString(6)
        //                , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
        //                , rdr.IsDBNull(8) ? (DateTime?)null : rdr.GetDateTime(8)
        //                , rdr.IsDBNull(9) ? "" : rdr.GetString(9)
        //                , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
        //                , rsInfo//rdr.IsDBNull(11) ? "" : rdr.GetString(11)
        //                , rdr.IsDBNull(12) ? "" : rdr.GetString(12)
        //                , rdr.IsDBNull(13) ? "" : rdr.GetString(13)
        //                , rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14)
        //                , rdr.IsDBNull(15) ? (int?)null : rdr.GetInt32(15)
        //                );
        //            infoList.Add(Info);
        //        }
        //        return infoList; 
        //    }
        //    catch
        //    {
        //        return null;
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.CloseReader();
        //        dbManager.Dispose();
        //    }
        //}
       
        public void Insert(RetirementInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@EmpTypeSeq", Info.EmployeeType == null ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(1, "@RetireYear", Info.BGYear);
                dbManager.AddParameters(2, "@StartDate", Info.StartDate);
                dbManager.AddParameters(3, "@JobSiteSeq", Info.JobSite == null ? (int?)null : Info.JobSite.Sequence);
                dbManager.AddParameters(4, "@RefNo", Info.RefNo);
                dbManager.AddParameters(5, "@Title", Info.Title);
                dbManager.AddParameters(6, "@RetireDescr", Info.Description);
                dbManager.AddParameters(7, "@ApprovedName", Info.ApprovedName);
                dbManager.AddParameters(8, "@ApprovedPosition", Info.ApprovedPosition);
                dbManager.AddParameters(9, "@ApprovedDate", Info.ApprovedDate);
                dbManager.AddParameters(10, "@RetireStatus", Info.Status.Status);
                dbManager.AddParameters(11, "@Remarks", Info.Remarks);
                dbManager.AddParameters(12, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(14, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_RETIREMENTS(EMP_TYPE_SEQ, RETIRE_YEAR, START_DATE"
                        + ", JOB_SITE_SEQ, REF_NO, RETIRE_TITLE, RETIRE_DESCR, APPROVED_NAME, APPROVED_POSITION"
                        + ", APPROVED_DATE, RETIRE_STATUS, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@EmpTypeSeq, @RetireYear, @StartDate, @JobSiteSeq, @RefNo, @Title"
                        + ", @RetireDescr, @ApprovedName, @ApprovedPosition, @ApprovedDate"
                        + ", @RetireStatus, @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(RetirementInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@RetireSeq", Info.Sequence);
                dbManager.AddParameters(1, "@EmpTypeSeq", Info.EmployeeType == null ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(2, "@RetireYear", Info.BGYear);
                dbManager.AddParameters(3, "@StartDate", Info.StartDate);
                dbManager.AddParameters(4, "@JobSiteSeq", Info.JobSite == null ? (int?)null : Info.JobSite.Sequence);
                dbManager.AddParameters(5, "@RefNo", Info.RefNo);
                dbManager.AddParameters(6, "@Title", Info.Title);
                dbManager.AddParameters(7, "@RetireDescr", Info.Description);
                dbManager.AddParameters(8, "@ApprovedName", Info.ApprovedName);
                dbManager.AddParameters(9, "@ApprovedPosition", Info.ApprovedPosition);
                dbManager.AddParameters(10, "@ApprovedDate", Info.ApprovedDate);
                dbManager.AddParameters(11, "@RetireStatus", Info.Status.Status);
                dbManager.AddParameters(12, "@Remarks", Info.Remarks);
                dbManager.AddParameters(13, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(14, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_RETIREMENTS"
                           + " SET EMP_TYPE_SEQ = @EmpTypeSeq"
                                + ", RETIRE_YEAR = @RetireYear"
                                + ", START_DATE = @StartDate"
                                + ", JOB_SITE_SEQ = @JobSiteSeq"
                                + ", REF_NO = @RefNo"
                                + ", RETIRE_TITLE = @Title"
                                + ", RETIRE_DESCR = @RetireDescr"
                                + ", APPROVED_NAME = @ApprovedName"
                                + ", APPROVED_POSITION = @ApprovedPosition"
                                + ", APPROVED_DATE = @ApprovedDate"
                                + ", RETIRE_STATUS = @RetireStatus"
                                + ", REMARKS = @Remarks"
                                + ", UPDATED_BY = @UpdatedBy"
                                + ", UPDATED_WHEN = @UpdatedWhen"
                           + " WHERE RETIRE_SEQ = @RetireSeq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdateWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_RETIREMENTS"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdateWhen"
                    + " WHERE RETIRE_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
